﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FWMS.DAO
{
    class Chukuguanli_tiaoweiliaoDao
    {
        //原材料查询全部
        public Bean.Chukuguanli_tiaoweiliaoBean chukuguanli_tiaoweiliaoDaoSelectchukuuse()
        {
            DB.DBbase dbbase = new DB.DBbase();
            Bean.Chukuguanli_tiaoweiliaoBean bean = new FWMS.Bean.Chukuguanli_tiaoweiliaoBean();
            string sql = "";
            sql = "select cailiaoname from cailiaoguanli where cailiaotype = '调味料'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //条件查询
        public Bean.Chukuguanli_tiaoweiliaoBean chukuguanli_tiaoweiliaoDaoSelectConditionchukuuse(Bean.Chukuguanli_tiaoweiliaoBean bean)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select chuku_yanshou,chuku_pinming,chuku_guige,chuku_danwei,chuku_danzhong,chuku_jianshu,chuku_zhongliang,chuku_beizhu from chukuguanli where chuku_type = '1'";
            sql = sql + " and chuku_yanshou= '" + bean.Chuku_yanshou + "'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //条件查询 库存管理用
        public Bean.Chukuguanli_tiaoweiliaoBean chukuguanli_tiaoweiliao_kucunuseDaoSelectConditionchukuuse(Bean.Chukuguanli_tiaoweiliaoBean bean)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = "";
            sql = "select chuku_pinming,chuku_guige,chuku_danzhong,chuku_jianshu,chuku_zhongliang,chuku_danwei from chukuguanli where chuku_type = '1'";
            sql = sql + " and chuku_yanshou= '" + bean.Chuku_yanshou + "'";
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    bean.Selectresult = dbbase.rows;
                    bean.Resultclo = dbbase.Col_Number;
                    bean.Resultrow = dbbase.Row_Number;
                }
                else
                {
                    bean.Selectresult = null;
                }
            }
            else
            {
                bean.Selectresult = null;
            }
            return bean;
        }
        //入库调味料添加
        public bool chukuguanli_tiaoweiliaoDaoAddchukuuse(Bean.Chukuguanli_tiaoweiliaoBean bean)
        {
            DB.DBbase dbbase = new DB.DBbase();
            string sql = ""; // as user 
            sql = "delete from chukuguanli where chuku_yanshou='" + bean.Chuku_yanshou + "' and chuku_type='" + bean.Chuku_type + "'";
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            for (int i = 0; i < (bean.Selectresult.Length / 8); i++)
            {
                if ((bean.Selectresult[i, 0] == null) || ("".Equals(bean.Selectresult[i, 0])))
                {
                    continue;
                }
                sql = "insert into chukuguanli( chuku_type, chuku_zhiban, chuku_yanshou, chuku_pinming, chuku_guige, chuku_danwei,chuku_danzhong, chuku_jianshu, chuku_zhongliang,chuku_no,  chuku_beizhu) values('" +
                bean.Chuku_type + "','" +
                bean.Chuku_zhiban + "','" +
                bean.Chuku_yanshou + "','" +
                bean.Selectresult[i, 0] + "','" +
                bean.Selectresult[i, 1] + "','" +
                bean.Selectresult[i, 2] + "','" +
                bean.Selectresult[i, 3] + "','" +
                bean.Selectresult[i, 4] + "','" +
                bean.Selectresult[i, 5] + "','" +
                bean.Selectresult[i, 6] + "','" +
                bean.Selectresult[i, 7] + "')";
                if (!dbbase.ExecuteSQL(sql.Trim()))
                {
                    return false;
                }
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //判断是修改还是添加
        public bool kucunguanli_insertormodifyadd(Bean.Chukuguanli_tiaoweiliaoBean bean)
        {
            string sql = "";
            Bean.KucunguanliBean kucunconditionbean = new FWMS.Bean.KucunguanliBean();
            for (int i = 0; i < bean.Selectresult.Length / 6; i++)
            {
                //查看库存中是否有时间段内出入库材料 chuku_pinming,chuku_guige,chuku_danzhong,chuku_jianshu,chuku_zhongliang,chuku_danwei
                if ("".Equals(bean.Selectresult[i, 1].ToString().Trim()))
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '1' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and (guige = '' or guige is null)";
                }
                else
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '1' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and guige = '" + bean.Selectresult[i, 1].ToString().Trim() + "'";
                }
                object[,] result = (object[,])kucunguanli_select(sql);
                if (result == null || "".Equals(COMMAND.Command.toString(result[0, 2])))
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Danwei = COMMAND.Command.toString(bean.Selectresult[i, 5]);
                    kucunconditionbean.Thismonthgongjin = COMMAND.Command.nulltozero(bean.Selectresult[i, 2]);
                    kucunconditionbean.Thismonthjianshu = COMMAND.Command.nulltozero(bean.Selectresult[i, 3]);
                    kucunconditionbean.Thismonthzhongliang = COMMAND.Command.nulltozero(bean.Selectresult[i, 4]);
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //添加
                    if (!kucunguanli_insert(kucunconditionbean))
                    {
                        return false;//INSERT失败
                    }
                }
                else
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Thismonthgongjin = (Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 2])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 0])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthjianshu = (int.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 3])) + int.Parse(COMMAND.Command.nulltozero(result[0, 1]))).ToString().Trim();
                    kucunconditionbean.Thismonthzhongliang = (Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 4])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 2])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");

                    //修改
                    if (!kucunguanli_update(kucunconditionbean))
                    {
                        return false;//更新失败
                    }
                }
            }
            return true;
        }
        //判断是修改还是添加
        public bool kucunguanli_insertormodifyjian(Bean.Chukuguanli_tiaoweiliaoBean bean)
        {
            string sql = "";
            Bean.KucunguanliBean kucunconditionbean = new FWMS.Bean.KucunguanliBean();
            for (int i = 0; i < bean.Selectresult.Length / 6; i++)
            {
                //查看库存中是否有时间段内出入库材料 chuku_pinming,chuku_guige,chuku_danzhong,chuku_jianshu,chuku_zhongliang,chuku_danwei
                if ("".Equals(bean.Selectresult[i, 1].ToString().Trim()))
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '1' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and (guige = '' or guige is null)";
                }
                else
                {
                    sql = "select thismonthgongjin,thismonthjianshu,thismonthzhongliang from kucunguanli where cailiao_type = '1' and pinming = '" + bean.Selectresult[i, 0].ToString().Trim() + "' and guige = '" + bean.Selectresult[i, 1].ToString().Trim() + "'";
                }

                object[,] result = (object[,])kucunguanli_select(sql);
                if (result == null || "".Equals(COMMAND.Command.toString(result[0, 2])))
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Danwei = COMMAND.Command.toString(bean.Selectresult[i, 5]);
                    kucunconditionbean.Thismonthgongjin = (-1 * float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 2]))).ToString().Trim();
                    kucunconditionbean.Thismonthjianshu = (-1 * int.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 3]))).ToString().Trim();
                    kucunconditionbean.Thismonthzhongliang = (-1 * float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 4]))).ToString().Trim();
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");
                    //添加
                    if (!kucunguanli_insert(kucunconditionbean))
                    {
                        return false;//INSERT失败
                    }
                }
                else
                {
                    kucunconditionbean.Pinming = COMMAND.Command.toString(bean.Selectresult[i, 0]);
                    kucunconditionbean.Guige = COMMAND.Command.toString(bean.Selectresult[i, 1]);
                    kucunconditionbean.Thismonthgongjin = (-1 * Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 2])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 0])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthjianshu = (-1 * int.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 3])) + int.Parse(COMMAND.Command.nulltozero(result[0, 1]))).ToString().Trim();
                    kucunconditionbean.Thismonthzhongliang = (-1 * Math.Round(float.Parse(COMMAND.Command.nulltozero(bean.Selectresult[i, 4])), 3) + Math.Round(float.Parse(COMMAND.Command.nulltozero(result[0, 2])), 3)).ToString().Trim();
                    kucunconditionbean.Thismonthsavedate = System.DateTime.Now.ToString("yyyyMMdd");

                    //修改
                    if (!kucunguanli_update(kucunconditionbean))
                    {
                        return false;//更新失败
                    }
                }
            }
            return true;
        }
        //库存修改数据 加法
        public bool kucunguanli_update(object condition)
        {
            string sql = "";
            Bean.KucunguanliBean kucunbean = (Bean.KucunguanliBean)condition;
            if ("".Equals(COMMAND.Command.toString(kucunbean.Guige)))
            {
                 sql = "update kucunguanli set thismonthgongjin = '" + kucunbean.Thismonthgongjin +
                                      "',thismonthjianshu = '" + kucunbean.Thismonthjianshu +
                                      "', thismonthzhongliang = '" + kucunbean.Thismonthzhongliang +
                                      "'  where pinming = '" + kucunbean.Pinming.ToString().Trim() + "' and (guige is null or guige= '') and cailiao_type= '1'";
            }
            else
            {
                 sql = "update kucunguanli set thismonthgongjin = '" + kucunbean.Thismonthgongjin +
                                      "',thismonthjianshu = '" + kucunbean.Thismonthjianshu +
                                      "', thismonthzhongliang = '" + kucunbean.Thismonthzhongliang +
                                      "'  where pinming = '" + kucunbean.Pinming.ToString().Trim() + "' and guige= '" + kucunbean.Guige.ToString().Trim() + "' and cailiao_type= '1'";
            }
           
            if (!kucunguanli_addmodify(sql))
            {
                //更新失败；
                return false;
            }
            return true;
        }
        //库存添加数据
        public bool kucunguanli_insert(object condition)
        {
            DB.DBbase dbbase = new DB.DBbase();

            Bean.KucunguanliBean bean = (Bean.KucunguanliBean)condition;
            string sql = ""; // as user 
            sql = "insert into kucunguanli( pinming, guige,danwei,thismonthgongjin, thismonthjianshu, thismonthzhongliang,thismonthsavedate, cailiao_type ) values('" +
                bean.Pinming + "','" +
                bean.Guige + "','" +
                bean.Danwei + "','" +
                bean.Thismonthgongjin + "','" +
                bean.Thismonthjianshu + "','" +
                bean.Thismonthzhongliang + "','" +
                bean.Thismonthsavedate + "','1' )";
            if (kucunguanli_addmodify(sql))
            {
                return true;//添加成功
            }
            else
            {
                return false;//添加失败
            }
        }
        //执行查询SQL
        private object kucunguanli_select(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (dbbase.ExecuteSQL(sql.Trim()))
            {
                if (dbbase.rows.Length != 0)
                {
                    return dbbase.rows;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }
        //执行添加修改SQL
        private bool kucunguanli_addmodify(string sql)
        {
            DB.DBbase dbbase = new DB.DBbase();
            if (!dbbase.ExecuteSQL(sql.Trim()))
            {
                return false;
            }
            if (dbbase.command())
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
}
